/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sysdental.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import sysdental.common.GestionDB;
import sysdental.to.TratamientoTO;

public class TratamientoManager {
    GestionDB cnn=new GestionDB();
    
    //-------------------------Método para listar tratamientos ------------------
    public List<TratamientoTO> listar() throws Exception{
        ArrayList data=new ArrayList();
        String sql="SELECT * FROM sd_tratamiento";
        try{
         cnn.conectar();
         ResultSet rs=cnn.ejecutarConsulta(sql);
         while(rs.next()){
             TratamientoTO p=new TratamientoTO();
             p.setId_tratamiento(rs.getString("id_tratamiento"));
             p.setDes_se(rs.getString("des_se"));
             p.setCos_se(rs.getString("cos_se"));
             data.add(p);
         }
         rs.close();
         cnn.desconectar();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
    return data;            
    }
    
    //------------------------- Método para agregar tratamientos
    public void agregar(TratamientoTO data) throws Exception{
        String sql="INSERT INTO sd_tratamiento (id_tratamiento,des_se,cos_se) VALUES (?,?,?)";
        Connection c=null;
        try{
            c=cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps=c.prepareStatement(sql);
            ps.setString(1,data.getId_tratamiento());
            ps.setString(2, data.getDes_se());
            ps.setString(3, data.getCos_se());
            ps.executeUpdate();
            c.commit();
            ps.close();
            
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        }finally{
            cnn.desconectar();
        }
    }
    
    //-------------------------Buscar por Id tratamientos para Editar--------
    public TratamientoTO buscarPorId(Object id) throws Exception{
        TratamientoTO t=null;
        String sql="SELECT * FROM sd_tratamiento WHERE id_tratamiento="+id;
        try{
            cnn.conectar();
            ResultSet rs=cnn.ejecutarConsulta(sql);
            if(rs.next()){
                t=new TratamientoTO();
                t.setId_tratamiento(rs.getString("id_tratamiento"));
                t.setDes_se(rs.getString("des_se"));
                t.setCos_se(rs.getString("cos_se"));
            }
            rs.close();
            cnn.desconectar();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null,e.getMessage());
        }        
        return t;
    }
    
    //------------------------- Editar Tratamiento --------------
    public void editar(TratamientoTO data) throws Exception{
        String sql="UPDATE sd_tratamiento SET des_se=?,cos_se=? WHERE id_tratamiento=?";
        Connection c=null;
        try{
            c=cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps=c.prepareStatement(sql);
            ps.setString(3,data.getId_tratamiento());
            ps.setString(1, data.getDes_se());
            ps.setString(2, data.getCos_se());
            ps.executeUpdate();
            c.commit();
            ps.close();
            
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        }finally{
            cnn.desconectar();
        }
    }
    
    
    //----------------------  Eliminar Tratamiento ----------------
    public void eliminar(String id) throws Exception{
        try{
            Connection c=cnn.conectar();
            String sql="DELETE FROM sd_tratamiento WHERE id_tratamiento=?";
            PreparedStatement ps=c.prepareStatement(sql);
            ps.setString(1,id);
            ps.executeUpdate();
            ps.close();
            
        }catch(Exception e){
            JOptionPane.showMessageDialog(null,e.getMessage());
        }finally{
            cnn.desconectar();
        }
    }
    
     //-------------------------Buscar por Id tratamientos para Editar--------
    public List<TratamientoTO> buscarPorDescripcion(Object des) throws Exception{
        ArrayList data=new ArrayList();
        TratamientoTO t=null;
        String sql="SELECT * FROM sd_tratamiento WHERE des_se LIKE '"+des+"%'";
        try{
            cnn.conectar();
            ResultSet rs=cnn.ejecutarConsulta(sql);
            while(rs.next()){
                t=new TratamientoTO();
                t.setId_tratamiento(rs.getString("id_tratamiento"));
                t.setDes_se(rs.getString("des_se"));
                t.setCos_se(rs.getString("cos_se"));
                data.add(t);
            }
            rs.close();
            cnn.desconectar();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null,e.getMessage());
        }        
        return data;
    }
}
